<?php
  class searchQuery
  {
      private $db;
      public function __construct()
      {
          $this -> db = new dataBase();
      }
      
      public function searchPersonSimple($profession)
      {
          $query = "SELECT u.* FROM users u, users_professions up, professions p
                    WHERE u.id=up.user_id AND p.id=up.profession_id AND lower(up.name) LIKE ?";
          
          $result = $this -> db -> PdoQuery($query, array($profession));
          return $result;
      }
      
      public function getUsersWithPhone()
      {
          $query = "SELECT * FROM users WHERE phone IS NOT NULL";
          $res = $this -> db -> PdoQuery($query, array());
          
          return $res;
      }
      
      public function getPersonFromCategory($post)
      {
          $values = array(strtolower($post['lookingPerson']));
          $query = "SELECT u.name, u.surname, u.phone, s.* 
              FROM service s, users u
              WHERE u.flag_confirm=true
              AND s.user_id = u.id
              AND category_id=(SELECT id FROM categories WHERE lower(name) = ?)";

          if(isset($post['showAdvancedInput'])){

              if(isset($post['optCompany']) && !isset($post['optPerson'])){
                  $query .= " AND trim(both ' ' from s.company_name) != '' ";
              }

              if(!isset($post['optCompany']) && isset($post['optPerson'])){
                  $query .= " AND trim(both ' ' from s.company_name) = '' ";
              }

              if(isset($post['searchPerService'])){
                  $query .=" AND s.price_per_service < ?";
                  $values[] = $post['searchPerService'];
              }
              if(isset($post['searchPerHour'])){
                  $query .=" AND s.price_per_hour < ?";
                  $values[] = $post['searchPerHour'];
              }
          }

          $result = $this -> db -> PdoQuery($query, $values);

          return $result; 
      }
      
      public function getGeo($id)
      {
          $query = "SELECT * FROM service_location WHERE service_id=? ORDER BY point_order";
          $result = $this -> db -> PdoQuery($query, array($id));
          
          return $result;
      }
      
      public function getUserData($id)
      {
            $query = "SELECT * FROM users WHERE id=?";
            $result = $this -> db -> PdoQuery($query, array($id));   
            
            return $result;
      }
      
      public function getUserDataFromService($id)
      {
          $query = "SELECT u.* FROM users u, service s WHERE s.user_id=u.id AND s.id=?";
          $result = $this -> db -> PdoQuery($query, array($id));
          
          return $result;
      }
      
      public function decreseSms($id)
      {
        $query = "UPDATE users SET active_sms=active_sms-1 WHERE id = ?";
        $this -> db -> PdoQuery($query, array($id));    
      }
      
      public function getCategoryFromName($name)
      {
          $query = "SELECT id FROM categories WHERE lower(name)=?";
          $result = $this -> db -> PdoQuery($query, array($name));
          
          return $result[0]['id'];
      }
      
      public function getLastOfferId()
      {
          $query = "SELECT max(id) as id FROM offers";
          $result = $this -> db -> PdoQuery($query, array());
          
          return $result[0]['id'];
      }
      
  }
?>
